
import os
import pandas as pd
import numpy as np

def merge_processed_indicator_files(processed_dir, output_file, year_range=None):
    if year_range is None:
        year_range = list(range(2009, 2024))  # 默认年份范围

    files = [f for f in os.listdir(processed_dir) if f.endswith("_AfterProcess.xlsx")]

    merged_df = None

    for file in files:
        file_path = os.path.join(processed_dir, file)
        df = pd.read_excel(file_path)

        # 将 '..' 替换为 NaN
        df.replace('..', np.nan, inplace=True)

        # 获取指标名
        indicator_name = [col for col in df.columns if col not in ['国名En', '国名Ch', 'Year']][0]

        df = df[['国名En', '国名Ch', 'Year', indicator_name]]

        if merged_df is None:
            merged_df = df
        else:
            merged_df = pd.merge(merged_df, df, on=['国名En', '国名Ch', 'Year'], how='outer')

    # 构造所有国家与年份组合
    country_pairs = merged_df[['国名En', '国名Ch']].drop_duplicates()
    full_index = pd.MultiIndex.from_product([country_pairs['国名En'], year_range], names=['国名En', 'Year'])
    full_df = full_index.to_frame(index=False).merge(country_pairs, on='国名En', how='left')

    # 合并并输出
    final_df = pd.merge(full_df, merged_df, on=['国名En', '国名Ch', 'Year'], how='left')
    final_df.sort_values(by=['国名En', 'Year'], inplace=True)
    final_df.to_excel(output_file, index=False)
    print(f"✅ 合并完成，输出文件：{output_file}")
